"""
数据库工具模块

提供SQLite数据库操作功能
"""

import sqlite3
import json
from datetime import datetime
from typing import List, Dict, Any, Optional
from pathlib import Path
from loguru import logger

from ..models.story_model import Story, StoryStyle, StoryQuality


class DatabaseManager:
    """数据库管理器"""

    def __init__(self, db_path: str = "data/stories.db"):
        """
        初始化数据库管理器
        
        Args:
            db_path: 数据库文件路径
        """
        self.db_path = db_path
        self._ensure_data_dir()
        self._init_database()

    def _ensure_data_dir(self):
        """确保数据目录存在"""
        data_dir = Path(self.db_path).parent
        data_dir.mkdir(exist_ok=True)

    def _init_database(self):
        """初始化数据库表"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 创建故事表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS stories (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        title TEXT NOT NULL,
                        content_zh TEXT,
                        content_en TEXT,
                        story_type TEXT NOT NULL,
                        language TEXT NOT NULL,
                        quality_score REAL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                ''')
                
                # 创建风格表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS story_styles (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        name TEXT NOT NULL,
                        description TEXT,
                        tone TEXT,
                        target_audience TEXT,
                        focus TEXT,
                        characters TEXT,
                        length_limit INTEGER,
                        bilingual BOOLEAN DEFAULT FALSE
                    )
                ''')
                
                # 创建质量评估表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS story_qualities (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        story_id INTEGER,
                        coherence_score REAL,
                        creativity_score REAL,
                        educational_score REAL,
                        language_score REAL,
                        overall_score REAL,
                        evaluated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (story_id) REFERENCES stories (id)
                    )
                ''')
                
                conn.commit()
                logger.info("数据库初始化完成")
                
        except Exception as e:
            logger.error(f"数据库初始化失败: {str(e)}")
            raise

    def save_story(self, story: Story) -> bool:
        """
        保存故事到数据库
        
        Args:
            story: 故事对象
            
        Returns:
            bool: 是否保存成功
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO stories (title, content_zh, content_en, story_type, language, quality_score, created_at)
                    VALUES (?, ?, ?, ?, ?, ?, ?)
                ''', (
                    story.title,
                    story.content_zh,
                    story.content_en,
                    story.story_type.value,
                    story.language.value,
                    story.quality_score,
                    story.created_at.isoformat()
                ))
                conn.commit()
                logger.info(f"故事保存成功: {story.title}")
                return True
                
        except Exception as e:
            logger.error(f"故事保存失败: {str(e)}")
            return False

    def get_story_by_id(self, story_id: int) -> Optional[Story]:
        """
        根据ID获取故事
        
        Args:
            story_id: 故事ID
            
        Returns:
            Optional[Story]: 故事对象
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, title, content_zh, content_en, story_type, language, quality_score, created_at
                    FROM stories WHERE id = ?
                ''', (story_id,))
                
                row = cursor.fetchone()
                if row:
                    return Story(
                        id=row[0],
                        title=row[1],
                        content_zh=row[2] or "",
                        content_en=row[3] or "",
                        story_type=row[4],
                        language=row[5],
                        quality_score=row[6],
                        created_at=datetime.fromisoformat(row[7])
                    )
                return None
                
        except Exception as e:
            logger.error(f"获取故事失败: {str(e)}")
            return None

    def get_stories_by_type(self, story_type: str) -> List[Story]:
        """
        根据类型获取故事列表
        
        Args:
            story_type: 故事类型
            
        Returns:
            List[Story]: 故事列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, title, content_zh, content_en, story_type, language, quality_score, created_at
                    FROM stories WHERE story_type = ?
                    ORDER BY created_at DESC
                ''', (story_type,))
                
                stories = []
                for row in cursor.fetchall():
                    story = Story(
                        id=row[0],
                        title=row[1],
                        content_zh=row[2] or "",
                        content_en=row[3] or "",
                        story_type=row[4],
                        language=row[5],
                        quality_score=row[6],
                        created_at=datetime.fromisoformat(row[7])
                    )
                    stories.append(story)
                
                return stories
                
        except Exception as e:
            logger.error(f"获取故事列表失败: {str(e)}")
            return []

    def get_all_stories(self) -> List[Story]:
        """
        获取所有故事
        
        Returns:
            List[Story]: 故事列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, title, content_zh, content_en, story_type, language, quality_score, created_at
                    FROM stories ORDER BY created_at DESC
                ''')
                
                stories = []
                for row in cursor.fetchall():
                    story = Story(
                        id=row[0],
                        title=row[1],
                        content_zh=row[2] or "",
                        content_en=row[3] or "",
                        story_type=row[4],
                        language=row[5],
                        quality_score=row[6],
                        created_at=datetime.fromisoformat(row[7])
                    )
                    stories.append(story)
                
                return stories
                
        except Exception as e:
            logger.error(f"获取所有故事失败: {str(e)}")
            return []

    def get_statistics(self) -> Dict[str, Any]:
        """
        获取数据库统计信息
        
        Returns:
            Dict[str, Any]: 统计信息
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 总故事数
                cursor.execute('SELECT COUNT(*) FROM stories')
                total_stories = cursor.fetchone()[0]
                
                # 按类型统计
                cursor.execute('''
                    SELECT story_type, COUNT(*) 
                    FROM stories 
                    GROUP BY story_type
                ''')
                stories_by_type = dict(cursor.fetchall())
                
                # 按语言统计
                cursor.execute('''
                    SELECT language, COUNT(*) 
                    FROM stories 
                    GROUP BY language
                ''')
                stories_by_language = dict(cursor.fetchall())
                
                # 平均质量评分
                cursor.execute('SELECT AVG(quality_score) FROM stories WHERE quality_score IS NOT NULL')
                avg_quality = cursor.fetchone()[0] or 0
                
                # 最新故事日期
                cursor.execute('SELECT MAX(created_at) FROM stories')
                latest_date = cursor.fetchone()[0]
                
                return {
                    "total_stories": total_stories,
                    "stories_by_type": stories_by_type,
                    "stories_by_language": stories_by_language,
                    "average_quality_score": round(avg_quality, 2),
                    "latest_story_date": latest_date
                }
                
        except Exception as e:
            logger.error(f"获取统计信息失败: {str(e)}")
            return {}

    def export_to_json(self, file_path: str) -> bool:
        """
        导出数据到JSON文件
        
        Args:
            file_path: 文件路径
            
        Returns:
            bool: 是否导出成功
        """
        try:
            stories = self.get_all_stories()
            stories_data = []
            
            for story in stories:
                story_dict = {
                    "id": story.id,
                    "title": story.title,
                    "content_zh": story.content_zh,
                    "content_en": story.content_en,
                    "story_type": story.story_type,
                    "language": story.language,
                    "quality_score": story.quality_score,
                    "created_at": story.created_at.isoformat()
                }
                stories_data.append(story_dict)
            
            with open(file_path, 'w', encoding='utf-8') as f:
                json.dump(stories_data, f, ensure_ascii=False, indent=2)
            
            logger.info(f"数据导出成功: {file_path}")
            return True
            
        except Exception as e:
            logger.error(f"数据导出失败: {str(e)}")
            return False 